package com.x.erp.dao;

import com.x.erp.model.Car;
import com.x.erp.model.CarSelectTemplate;
import com.x.erp.model.ESCar;
import com.x.erp.model.Model;
import com.x.erp.util.ConstantState;
import com.x.framework.dao.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
@Repository
public class CarDao extends BaseDao {
	private final static String selectCarListSql = "SELECT " +
			"ec.CAR_ID, "+
			"ec.CAR_TITLE, "+
			"ec.CAR_VIN, "+
			"ec.CAR_OUT_COLOR, "+
			"ec.CAR_IMAGE_URL, "+
			"ec.CAR_MILEAGE, "+
			"ec.CAR_RETAIL_PRICE, "+
			"ec.CAR_LICENCE_DATE, "+
			"ec.CAR_FACOTRY_DATE, "+
			"ec.CAR_FORCE_INSURANCE_DATE, "+
			"ec.CAR_LICENCE_STATE, "+
			"ec.CAR_ORDER_STATE, "+
			"ec.CAR_FIXED_PRICE_STATE, "+
			"ec.CAR_BARGAIN_PRICE_STATE, "+
			"ec.CAR_TRANSFER_STATE, "+
			"ec.CAR_SALE_STATE, "+
			"ec.CAR_SALE_TIME, "+
			"ec.CAR_DETECT_STATE, "+
			"ec.CAR_DISPLACEMENT, "+
			"ec.CAR_INLET_MODEL, "+
			"ec.CAR_GEARBOX, "+
			"ec.CAR_STOCK_STATE, "+
			"ec.CAR_STOCK_TIME, "+
			"ec.CAR_AUTH_STATE, "+
			"ec.CAR_RFID, "+
			"ec.CAR_RFID_TIME, "+
			"ec.CAR_PLEDGE_STATE, "+
			"ec.CAR_SITE_STATE, "+
			"ec.CAR_ENVIRON, "+
			"ec.CAR_SITE_TIME, "+
			"ec.CAR_EXTENDED_WARRANTY, "+
			"ec.CAR_FINANCE_SERVICE, "+
			"ec.CAR_CREATE_TIME, "+
			"ec.CAR_UPDATE_TIME, "+
			"ec.BRAND_ID, "+
			"ec.SERIES_ID, "+
			"ec.MODEL_ID, "+
			"ec.CAR_TYPE_ID, "+
			"ec.USER_ID, "+
			"ec.MARKET_ID, "+
			"ec.REGION_ID, "+
			"ec.SHOP_ID, "+
			"ec.MARKET_CAR_ID, "+
			"ec.SALE_USER_ID, "+
			"bb.BRAND_NAME, " +
			"bs.SERIES_NAME, " +
			"em.MARKET_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM, " +
			"es.SHOP_MANAGE_NAME, " +
			"es.SHOP_MANAGE_MOBILE, " +
			"bc.CITY_NAME, " +
			"eu.USER_NAME, "+
			"eu.USER_MOBILE, "+
			"ecs.SALE_CREATE_TIME, "+
			"eco.ORDER_CREATE_TIME, "+
			"elb.LOAN_BRAND_ID, "+
			"ecv1.VALUATION_DEALER_LOW_BUY_PRICE, "+
			"ecv1.VALUATION_DEALER_LOW_SOLD_PRICE, "+
			"ecv2.VALUATION_DEALER_HIGH_SOLD_PRICE, "+
			"ecv3.VALUATION_FAIL_REASON "+
			"FROM " +
			"ERP_CAR ec " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"JOIN x_base.base_model bm ON ec.model_id = bm.model_id " +
			"LEFT JOIN ERP_USER eu ON ec.SALE_USER_ID = eu.USER_ID "+
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN ERP_CAR_SALE ecs ON ec.CAR_ID = ecs.CAR_ID "+
			"LEFT JOIN ERP_CAR_ORDER eco ON ec.CAR_ID = eco.CAR_ID " +
			"LEFT JOIN base_city bc ON bc.city_id = em.city_id " +
			"LEFT JOIN erp_loan_brand elb ON ec.BRAND_ID = elb.LOAN_BRAND_ID " +
			"LEFT JOIN (SELECT ecv.VALUATION_DEALER_LOW_BUY_PRICE, ecv.VALUATION_DEALER_LOW_SOLD_PRICE, ecv.CAR_ID, ecv.VALUATION_FAIL_REASON FROM erp_car_valuation ecv WHERE ecv.VALUATION_CONDITION = 'normal' GROUP BY ecv.CAR_ID)ecv1 ON ec.CAR_ID = ecv1.CAR_ID " +
			"LEFT JOIN (SELECT ecv.VALUATION_DEALER_HIGH_SOLD_PRICE, ecv.CAR_ID, ecv.VALUATION_FAIL_REASON FROM erp_car_valuation ecv WHERE ecv.VALUATION_CONDITION = 'excellent' GROUP BY ecv.CAR_ID)ecv2 ON ec.CAR_ID = ecv2.CAR_ID " +
			"LEFT JOIN (SELECT ecv.CAR_ID, ecv.VALUATION_FAIL_REASON FROM erp_car_valuation ecv GROUP BY ecv.CAR_ID)ecv3 ON ec.CAR_ID = ecv3.CAR_ID " +
			"WHERE 1=1 ";

	/**
	 * @param car 封装了car的检索条件
	 * @return 满足条件的carlist
	 * @throws Exception
	 */
	public List<Car> selectCarList(Car car) throws Exception{
		StringBuilder sql = new StringBuilder(selectCarListSql);
		List<Object> params = new ArrayList<Object>();
		params.add(car.getMarketId());
		searchCondition(sql, params, car);
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private final static String selectCarNumSql = "SELECT " +
			"COUNT(ec.CAR_ID) " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.base_model bm ON ec.model_id = bm.model_id " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN base_city bc ON bc.city_id = em.city_id " +
			"WHERE 1=1 ";
	/**
	 * 查询车辆总数
	 * @param car
	 * @return
	 */
	public int selectCarNum(Car car) {
		StringBuilder sql = new StringBuilder(selectCarNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(car.getMarketId());
		searchCondition(sql, params, car);
		return this.queryForInt(sql.toString(), params);
	}

	private void searchCondition(StringBuilder sql, List<Object> params, Car car) {
		if(car != null){
			if(this.isString(car.getCarId())){
				sql.append("AND ec.CAR_ID = ? ");
				params.add(car.getCarId());
			}
			if(this.isString(car.getMarketCarId())){
				sql.append("AND ec.MARKET_CAR_ID = ? ");
				params.add(car.getMarketCarId());
			}
			if(this.isString(car.getShopId())){
				sql.append("AND ec.SHOP_ID = ? ");
				params.add(car.getShopId());
			}
			if(car.getBrandId() != null){
				sql.append("AND ec.BRAND_ID = ? ");
				params.add(car.getBrandId());
			}
			if(car.getCarTypeId() != null){
				sql.append("AND ec.CAR_TYPE_ID = ? ");
				params.add(car.getCarTypeId());
			}
			if(car.getChioceSuv ()){
				sql.append("AND ( ec.CAR_TYPE_ID = ? OR ec.CAR_TYPE_ID = ? OR ec.CAR_TYPE_ID = ?) ");
				params.add(9);
				params.add(10);
				params.add(11);
			}
			// 排量（起）
			if(car.getCarDisplacementFrom() != null){
				sql.append("AND ec.CAR_DISPLACEMENT >= ? ");
				params.add(car.getCarDisplacementFrom());
			}
			// 排量（止）
			if(car.getCarDisplacementTo() != null){
				sql.append("AND ec.CAR_DISPLACEMENT <= ? ");
				params.add(car.getCarDisplacementTo());
			}
			// 进气方式
			if(this.isString(car.getCarInletModel())){
				sql.append("AND ec.CAR_INLET_MODEL = ? ");
				params.add(car.getCarInletModel());
			}
			if(this.isString(car.getCarOutColor())){
				sql.append("AND ec.CAR_OUT_COLOR = ? ");
				params.add(car.getCarOutColor());
			}
			// 变速箱
			if(this.isString(car.getCarGearbox())){
				sql.append("AND ec.CAR_GEARBOX = ? ");
				params.add(car.getCarGearbox());
			}
			// 燃料
			if(this.isString(car.getCarFuel())){
				sql.append("AND ec.CAR_FUEL = ? ");
				params.add(car.getCarFuel());
			}
			// 车辆用途（1：营运、2：非营运、3营转非）
			if(car.getCarPurpose() != null){
				sql.append("AND ec.CAR_PURPOSE = ? ");
				params.add(car.getCarPurpose());
			}
			// 座位数
			Model modelTemp = car.getModel();
			if(modelTemp != null && this.isString(modelTemp.getModelSeat())){
				sql.append("AND bm.MODEL_SEAT = ? ");
				params.add(modelTemp.getModelSeat());
			}
			if(this.isString(car.getCarEnviron())){
				sql.append("AND ec.CAR_ENVIRON = ? ");
				params.add(car.getCarEnviron());
			}
			if(car.getCarLicenceDate() != null){
				sql.append("AND ec.CAR_LICENCE_DATE = ? ");
				params.add(car.getCarLicenceDate());
			}
			if(this.isString(car.getCarVin())){
				sql.append("AND ec.CAR_VIN = ? ");
				params.add(car.getCarVin());
			}
			if(this.isString(car.getCarVin6())){
				sql.append("AND ec.CAR_VIN6 = ? ");
				params.add(car.getCarVin6());
			}
			if(car.getModelId() != null){
				sql.append("AND ec.MODEL_ID = ? ");
				params.add(car.getModelId());
			}
			if(car.getSeriesId() != null){
				sql.append("AND ec.SERIES_ID = ? ");
				params.add(car.getSeriesId());
			}
			if(this.isString(car.getUserId())){
				sql.append("AND ec.USER_ID = ? ");
				params.add(car.getUserId());
			}
			if(car.getCarFloorPrice() != null){
				sql.append("AND ec.CAR_FLOOR_PRICE = ? ");
				params.add(car.getCarFloorPrice());
			}
			if(car.getCarRetailPrice() != null){
				sql.append("AND ec.CAR_RETAIL_PRICE = ? ");
				params.add(car.getCarRetailPrice());
			}
			if(car.getCarTaxPrice() != null){
				sql.append("AND ec.CAR_TAX_PRICE = ? ");
				params.add(car.getCarTaxPrice());
			}
			if(car.getCarMileage() != null){
				sql.append("AND ec.CAR_MILEAGE = ? ");
				params.add(car.getCarMileage());
			}
			if(car.getCarAuthState() != null){
				sql.append("AND ec.CAR_AUTH_STATE = ? ");
				params.add(car.getCarAuthState());
			}
			if(car.getCarDetectState() != null){
				// 如果查询车辆检测状态为已通过初检，将车辆检测状态为 >= 520 的都带出来
				if (car.getCarDetectState() == ConstantState.CAR_DETECT_STATE_FIRST_PASS){
					sql.append("AND ec.CAR_DETECT_STATE >= ? ");
					params.add(car.getCarDetectState());
				} else {
					sql.append("AND ec.CAR_DETECT_STATE = ? ");
					params.add(car.getCarDetectState());
				}
			}
			if(car.getCarSaleState() != null){
				sql.append("AND ec.CAR_SALE_STATE = ? ");
				params.add(car.getCarSaleState());
			}
			if(car.getCarStockState() != null){
				// 根据库存状态查询车辆信息，查询的是整十以内的数据，如传入10查询[10,20)的数据
				if(car.getCarStockState() % 10 == 0) {
					sql.append("AND ec.CAR_STOCK_STATE >= ? AND ec.CAR_STOCK_STATE < ? ");
					params.add(car.getCarStockState());
					params.add(car.getCarStockState() + 10);
				}else{
					sql.append("AND ec.CAR_STOCK_STATE = ? ");
					params.add(car.getCarStockState());
				}
			}
			if(car.getCarTransferState() != null){
				sql.append("AND ec.CAR_TRANSFER_STATE = ? ");
				params.add(car.getCarTransferState());
			}
			if(car.getCarOrderState() != null){
				sql.append("AND ec.CAR_ORDER_STATE = ? ");
				params.add(car.getCarOrderState());
			}
			if(car.getCarOrderState() != null){
				sql.append("AND ec.CAR_ORDER_STATE = ? ");
				params.add(car.getCarOrderState());
			}
			if(car.getCarBargainPriceState() != null){
				sql.append("AND ec.CAR_BARGAIN_PRICE_STATE = ? ");
				params.add(car.getCarBargainPriceState());
			}
			if(car.getFromFloorPrice() != null) {
				sql.append("AND ec.CAR_FLOOR_PRICE >= ? ");
				params.add(car.getFromFloorPrice());
			}
			if(car.getToFloorPrice() != null) {
				sql.append("AND ec.CAR_FLOOR_PRICE <= ? ");
				params.add(car.getToFloorPrice());
			}
			if(car.getFromRetailPrice() != null) {
				sql.append("AND ec.CAR_RETAIL_PRICE >= ? ");
				params.add(car.getFromRetailPrice());
			}
			if(car.getToRetailPrice() != null) {
				sql.append("AND ec.CAR_RETAIL_PRICE <= ? ");
				params.add(car.getToRetailPrice());
			}
			if(car.getFromMileage() != null) {
				sql.append("AND ec.CAR_MILEAGE >= ? ");
				params.add(car.getFromMileage());
			}
			if(car.getToMileage() != null) {
				sql.append("AND ec.CAR_MILEAGE <= ? ");
				params.add(car.getToMileage());
			}
			if(car.getFromCarAge() != null) {
				sql.append("AND ec.CAR_LICENCE_DATE <= ? ");
				Calendar c = Calendar.getInstance();
				c.add(Calendar.YEAR, -car.getFromCarAge());
				params.add(c.getTime());
			}
			if(car.getToCarAge() != null) {
				sql.append("AND ec.CAR_LICENCE_DATE >= ? ");
				Calendar c = Calendar.getInstance();
				c.add(Calendar.YEAR, -car.getToCarAge());
				params.add(c.getTime());
			}
			if(car.getFromCreateTime() != null) {
				sql.append("AND ec.CAR_CREATE_TIME <= ? ");
				params.add(getDate(-car.getFromCreateTime()));
			}
			if(car.getToCreateTime() != null) {
				sql.append("AND ec.CAR_CREATE_TIME >= ? ");
				params.add(getDate(-car.getToCreateTime()));
			}
			if(StringUtils.isNotEmpty(car.getCarRfid())) {
				sql.append("AND ec.CAR_RFID = ? ");
				params.add(car.getCarRfid());
			}
			if(car.getCarSiteState() != null) {
				sql.append("AND ec.CAR_SITE_STATE = ? ");
				params.add(car.getCarSiteState());
			}
			if(car.getCarPledgeState() != null) {
				sql.append("AND ec.CAR_PLEDGE_STATE = ? ");
				params.add(car.getCarPledgeState());
			}
			if(this.isString (car.getCityId())) {
				sql.append("AND bc.city_id = ? ");
				params.add(car.getCityId());
			}
			if(this.isString (car.getCarNo ())){
				sql.append("AND ec.CAR_NO = ? ");
				params.add(car.getCarNo());
			}
		}
	}

	private final static String selectCarSql = "SELECT " +
			"ec.*, " +
			"bca.CAR_AGE_REMARK, " +
			"bsa.STOCK_AGE_REMARK, " +
			"bb.BRAND_NAME, " +
			"bs.SERIES_NAME, " +
			"bm.MODEL_NAME, " +
			"bm.MODEL_YEAR, " +
			"er.REGION_NAME, " +
			"bct.CAR_TYPE_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"em.MARKET_NAME, " +
			"bc.CITY_NAME, " +
			"eu1.USER_NAME AS CREATE_USER_NAME, " +
			"eu1.USER_MOBILE AS CREATE_USER_MOBILE, " +
			"eu1.USER_REMARK AS CREATE_USER_REMARK, " +
			"eu.USER_NAME, "+
			"eu.USER_MOBILE, "+
			"elb.LOAN_BRAND_ID, "+
			"ecv1.VALUATION_DEALER_LOW_BUY_PRICE, "+
			"ecv1.VALUATION_DEALER_LOW_SOLD_PRICE, "+
			"ecv2.VALUATION_DEALER_HIGH_SOLD_PRICE, "+
			"ecv3.VALUATION_FAIL_REASON "+
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.BASE_MODEL bm ON ec.MODEL_ID = bm.MODEL_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"JOIN ERP_MARKET em ON es.MARKET_ID = em.MARKET_ID " +
			"JOIN BASE_CITY bc ON em.CITY_ID = bc.CITY_ID " +
			"JOIN BASE_CAR_TYPE bct ON ec.CAR_TYPE_ID = bct.CAR_TYPE_ID " +
			"LEFT JOIN ERP_USER eu1 ON ec.USER_ID = eu1.USER_ID " +
			"LEFT JOIN ERP_USER eu ON ec.SALE_USER_ID = eu.USER_ID "+
			"LEFT JOIN BASE_CAR_AGE bca ON ec.CAR_LICENCE_DATE >= bca.CAR_AGE_FROM AND ec.CAR_LICENCE_DATE < bca.CAR_AGE_TO " +
			"LEFT JOIN BASE_STOCK_AGE bsa ON ec.CAR_CREATE_TIME >= bsa.STOCK_AGE_FROM AND ec.CAR_CREATE_TIME < bsa.STOCK_AGE_TO " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN erp_loan_brand elb ON ec.BRAND_ID = elb.LOAN_BRAND_ID " +
			"LEFT JOIN (SELECT ecv.VALUATION_DEALER_LOW_BUY_PRICE, ecv.VALUATION_DEALER_LOW_SOLD_PRICE, ecv.CAR_ID, ecv.VALUATION_FAIL_REASON FROM erp_car_valuation ecv WHERE ecv.VALUATION_CONDITION = 'normal' GROUP BY ecv.CAR_ID)ecv1 ON ec.CAR_ID = ecv1.CAR_ID " +
			"LEFT JOIN (SELECT ecv.VALUATION_DEALER_HIGH_SOLD_PRICE, ecv.CAR_ID, ecv.VALUATION_FAIL_REASON FROM erp_car_valuation ecv WHERE ecv.VALUATION_CONDITION = 'excellent' GROUP BY ecv.CAR_ID)ecv2 ON ec.CAR_ID = ecv2.CAR_ID " +
			"LEFT JOIN (SELECT ecv.CAR_ID, ecv.VALUATION_FAIL_REASON FROM erp_car_valuation ecv GROUP BY ecv.CAR_ID)ecv3 ON ec.CAR_ID = ecv3.CAR_ID " +
			"WHERE 1=1 ";
	/**
	 * 查询车辆信息
	 * @param car
	 * @return
	 * @throws Exception
	 */
	public Car selectCar(Car car) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarSql);
		List<Object> params = new ArrayList<Object>();
		searchCondition(sql, params, car);
		sql.append ("LIMIT 1 ");
		sql = new StringBuilder("SELECT * FROM (").append(sql).append(") car ");
		List<Car> results = this.queryForList(sql.toString(), Car.class, params);
		if(results != null && results.size() > 0) {
			return results.get(0);
		}
		return null;
	}

	private final static String selectCarStorageInfoSql = "SELECT " +
			"COUNT(DISTINCT ec.CAR_ID) as storageNum " +
			"FROM " +
			"ERP_car ec, " +
			"erp_car_log ecl, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.car_id = ecl.car_id " +
			"AND CAR_STATE_KEY = 'carStockState' " +
			"AND CAR_STATE_VALUE = 20 " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询入库信息
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<String, Object> selectCarStorageInfo(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarStorageInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(car.getMarketId());
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecl.CAR_LOG_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecl.CAR_LOG_CREATE_TIME < ? ");
			params.add(endDate);
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	private final static String selectMarketStorageInfoSql = "SELECT "
			+ "COUNT(DISTINCT ec.CAR_ID) as storageNum, "
			+ "bc.CITY_NAME AS cityName, "
			+ "em.MARKET_NAME as marketName, "
			+ "em.MARKET_SHORT_NAME as marketShortName "
			+ "FROM "
			+ "ERP_car ec, "
			+ "erp_car_log ecl, "
			+ "ERP_market em, "
			+ "ERP_market emp, "
			+ "BASE_CITY bc "
			+ "WHERE "
			+ "ec.car_id = ecl.car_id "
			+ "AND CAR_STATE_KEY = 'carStockState' "
			+ "AND CAR_STATE_VALUE = 20 "
			+ "AND em.CITY_ID = bc.CITY_ID "
			+ "AND ec.MARKET_ID = em.MARKET_ID "
			+ "AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 市场入库信息
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectMarketCarStorageInfo(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectMarketStorageInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(car.getMarketId());
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecl.CAR_LOG_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecl.CAR_LOG_CREATE_TIME < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY ec.MARKET_ID ");
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("cityName", rs.getString("cityName"));
				map.put("marketName", rs.getString("marketName"));
				map.put("marketShortName", rs.getString("marketShortName"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	/**
	 * 入库统计查询条件
	 * @param sql
	 * @param params
	 * @param car
	 * @param startDate
	 * @param endDate
	 */
	private void statisSearchCondition(StringBuilder sql, List<Object> params, Car car, Date startDate, Date endDate) {
		if(car != null){
			if(this.isString(car.getCarId())){
				sql.append("AND ec.CAR_ID = ? ");
				params.add(car.getCarId());
			}
			if(this.isString(car.getShopId())){
				sql.append("AND ec.SHOP_ID = ? ");
				params.add(car.getShopId());
			}
			if(car.getBrandId() != null){
				sql.append("AND ec.BRAND_ID = ? ");
				params.add(car.getBrandId());
			}
			if(car.getCarTypeId() != null){
				sql.append("AND ec.CAR_TYPE_ID = ? ");
				params.add(car.getCarTypeId());
			}
			if(car.getModelId() != null){
				sql.append("AND ec.MODEL_ID = ? ");
				params.add(car.getModelId());
			}
			if(car.getSeriesId() != null){
				sql.append("AND ec.SERIES_ID = ? ");
				params.add(car.getSeriesId());
			}
			if(this.isString(car.getUserId())){
				sql.append("AND ec.USER_ID = ? ");
				params.add(car.getUserId());
			}
			if(car.getCarStockState() != null){
				sql.append("AND ec.CAR_STOCK_STATE = ? ");
				params.add(car.getCarStockState());
			}
		}
		if(startDate != null) {
			sql.append("AND ec.CAR_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ec.CAR_CREATE_TIME < ? ");
			params.add(endDate);
		}
	}

	/**
	 * 排序
	 * @param sql
	 * @param car
	 */
	private void orderCondition(StringBuilder sql, Car car) {
		if(StringUtils.isNotEmpty(car.getOrderColunm())) {
			if(car.getOrderColunm ().equalsIgnoreCase ("DEFAULT_ORDER")){
				sql.append ("ORDER BY (CASE bc.city_id WHEN " + car.getOrderColunmValue () + " THEN 0 ELSE 1 END) ASC,ec.CAR_SALE_TIME DESC ");
			} else {
				sql.append ("ORDER BY " + car.getOrderColunm () + " ");
				if (StringUtils.isNotEmpty (car.getOrderMode ())) {
					sql.append (car.getOrderMode () + " ");
				}
			}
		}else {
			sql.append("ORDER BY ec.CAR_CREATE_TIME DESC ");
		}
	}

	private final static String selectCarOutStorageInfoSql = "SELECT " +
			"COUNT(ecs.SALE_ID) AS storageNum, " +
			"SUM(ecs.SALE_PRICE) AS SALE_VOLUME " +
			"FROM " +
			"ERP_CAR ec, " +
			"ERP_CAR_SALE ecs, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.car_id = ecs.car_id " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询车辆出库信息
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<String, Object> selectCarOutStockInfo(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOutStorageInfoSql);
		List<Object> params = new ArrayList<Object>();
		params.add(car.getMarketId());
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("saleVolume", rs.getBigDecimal("SALE_VOLUME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	private final static String selectCarOrderInfoSql = "SELECT " +
			"COUNT(eco.ORDER_ID) AS storageNum " +
			"FROM " +
			"ERP_CAR ec, " +
			"ERP_CAR_ORDER eco " +
			"WHERE " +
			"ec.CAR_STOCK_STATE > 10 " +
			"AND ec.CAR_STOCK_STATE < 40 " +
			"AND ec.car_id = eco.car_id ";
	/**
	 * 查询车辆预订信息
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<String, Object> selectCarOrderInfo(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOrderInfoSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	private final static String selectStorageBrandRankSql = "SELECT "
			+ "COUNT(ec.CAR_ID) as storageNum, "
			+ "bb.BRAND_NAME "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "erp_market em, "
			+ "erp_market emp, "
			+ "x_base.BASE_BRAND bb ";
	private final static String getSelectStorageBrandRankWhereSql = "WHERE "
			+ "ec.BRAND_ID = bb.BRAND_ID "
			+ "AND ec.MARKET_ID = em.MARKET_ID "
			+ "AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询库存品牌排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectStorageBrandRank(Car car, Date startDate, Date endDate, List<Car> cars) {
		StringBuilder sql = getCarListSql(selectStorageBrandRankSql, getSelectStorageBrandRankWhereSql, cars);
		List<Object> params = new ArrayList<>();
		params.add(car.getMarketId());
		statisSearchCondition(sql, params, car, startDate, endDate);
		sql.append("GROUP BY ec.BRAND_ID ");
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("brandName", rs.getString("BRAND_NAME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOutStockBrandRankSql = "SELECT "
			+ "COUNT(ec.CAR_ID) as storageNum, "
			+ "bb.BRAND_NAME "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "ERP_CAR_SALE ecs, "
			+ "erp_market em, "
			+ "erp_market emp, "
			+ "x_base.BASE_BRAND bb "
			+ "WHERE "
			+ "ec.CAR_ID = ecs.CAR_ID "
			+ "AND ec.BRAND_ID = bb.BRAND_ID "
			+ "AND ec.MARKET_ID = em.MARKET_ID "
			+ "AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询出库品牌排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOutStockBrandRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOutStockBrandRankSql);
		List<Object> params = new ArrayList<>();
		params.add(car.getMarketId());
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY ec.BRAND_ID ");
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("brandName", rs.getString("BRAND_NAME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOrderBrandRankSql = "SELECT "
			+ "COUNT(ec.CAR_ID) as storageNum, "
			+ "bb.BRAND_NAME "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "ERP_CAR_ORDER eco, "
			+ "x_base.BASE_BRAND bb "
			+ "WHERE "
			+ "ec.CAR_ID = eco.CAR_ID "
			+ "AND ec.BRAND_ID = bb.BRAND_ID ";
	/**
	 * 查询出库品牌排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOrderBrandRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOrderBrandRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY ec.BRAND_ID ");
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("brandName", rs.getString("BRAND_NAME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectStorageSeriesRankSql = "SELECT "
			+ "COUNT(ec.CAR_ID) as storageNum, "
			+ "bs.SERIES_NAME "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "x_base.BASE_SERIES bs ";
	private final static String selectStorageSeriesRankWhereSql = "WHERE "
			+ "ec.SERIES_ID = bs.SERIES_ID ";
	/**
	 * 查询库存车系排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectStorageSeriesRank(Car car, Date startDate, Date endDate, List<Car> cars) {
		StringBuilder sql = getCarListSql(selectStorageSeriesRankSql, selectStorageSeriesRankWhereSql, cars);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		sql.append("GROUP BY ec.SERIES_ID ");
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("seriesName", rs.getString("SERIES_NAME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOutStockSeriesRankSql = "SELECT "
			+ "COUNT(ecs.SALE_ID) AS storageNum, "
			+ "SUM(ecs.SALE_PRICE) AS SALE_VOLUME, "
			+ "bb.BRAND_NAME, "
			+ "bs.SERIES_NAME "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "ERP_CAR_SALE ecs, "
			+ "x_base.BASE_SERIES bs, "
			+ "x_base.BASE_BRAND bb, "
			+ "erp_market em, "
			+ "erp_market emp "
			+ "WHERE "
			+ "ec.CAR_ID = ecs.CAR_ID "
			+ "AND ec.SERIES_ID = bs.SERIES_ID "
			+ "AND bs.BRAND_ID = bb.BRAND_ID "
			+ "AND ec.MARKET_ID = em.MARKET_ID "
			+ "AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询出库车系排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOutStockSeriesRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOutStockSeriesRankSql);
		List<Object> params = new ArrayList<>();
		params.add(car.getMarketId());
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY bs.SERIES_ID,bs.BRAND_ID ");
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("seriesName", rs.getString("SERIES_NAME"));
				map.put("brandName", rs.getString("BRAND_NAME"));
				map.put("saleVolume", rs.getBigDecimal("SALE_VOLUME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOrderSeriesRankSql = "SELECT "
			+ "COUNT(ec.CAR_ID) as storageNum, "
			+ "bs.SERIES_NAME "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "ERP_CAR_ORDER eco, "
			+ "x_base.BASE_SERIES bs "
			+ "WHERE "
			+ "ec.CAR_ID = eco.CAR_ID "
			+ "AND ec.SERIES_ID = bs.SERIES_ID ";
	/**
	 * 查询出库车系排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOrderSeriesRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOrderSeriesRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY ec.SERIES_ID ");
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("seriesName", rs.getString("SERIES_NAME"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectStorageCarAgeRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as storageNum, " +
			"bca.CAR_AGE_REMARK " +
			"FROM " +
			"ERP_car ec, " +
			"base_car_age bca ";
	private final static String selectStorageCarAgeRankWhereSql = "WHERE " +
			"ec.CAR_LICENCE_DATE >= bca.car_age_from " +
			"AND ec.CAR_LICENCE_DATE <= bca.car_age_to ";
	/**
	 * 查询库存车龄排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectStorageCarAgeRank(Car car, Date startDate, Date endDate, List<Car> cars) {
		StringBuilder sql = getCarListSql(selectStorageCarAgeRankSql, selectStorageCarAgeRankWhereSql, cars);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		sql.append("GROUP BY bca.CAR_AGE_ID " );
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("carAgeRemark", rs.getString("CAR_AGE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectStoragePriceRankSql = "SELECT "
			+ "COUNT(ec.CAR_ID) as storageNum, "
			+ "bp.PRICE_REMARK "
			+ "FROM "
			+ "ERP_CAR ec, "
			+ "BASE_PRICE bp ";
	private final static String selectStoragePriceRankWhereSql = "WHERE "
			+ "ec.CAR_RETAIL_PRICE >= bp.PRICE_FROM_10000 AND ec.CAR_RETAIL_PRICE <= bp.PRICE_TO_10000 ";
	/**
	 * 查询库存价格排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectStoragePriceRank(Car car, Date startDate, Date endDate, List<Car> cars) {
		StringBuilder sql = getCarListSql(selectStoragePriceRankSql, selectStoragePriceRankWhereSql, cars);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		sql.append("GROUP BY bp.PRICE_ID ");
		sql.append("ORDER BY storageNum DESC ");
		getLimitSql(sql, car.getPageSize());
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("priceRemark", rs.getString("PRICE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarRadarInfoSql = "SELECT "
			+ "MAX(ec.CAR_RETAIL_PRICE) AS CAR_RETAIL_PRICE, "
			+ "MIN(ec.CAR_LICENCE_DATE) AS CAR_AGE, "
			+ "MAX(ec.CAR_DISPLACEMENT + 0) AS CAR_DISPLACEMENT, "
			+ "MAX(ec.CAR_MILEAGE) AS CAR_MILEAGE ,"
			+ "MAX(ec.CAR_TRANSFER_NUM) AS CAR_TRANSFER_NUM "
			+ "FROM "
			+ "erp_car ec ";
	private final static String selectCarRadarInfoWhereSql = "WHERE 1=1 ";
	/**
	 * 查询车辆雷达图信息
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @param cars
	 * @return
	 */
	public Map<String,Object> selectCarRadarInfo(Car car, Date startDate, Date endDate, List<Car> cars) {
		StringBuilder sql = getCarListSql(selectCarRadarInfoSql, selectCarRadarInfoWhereSql, cars);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("carRetailPrice", rs.getBigDecimal("CAR_RETAIL_PRICE"));
				map.put("carAge", 0);
				if(rs.getDate("CAR_AGE") != null) {
					int carAge = Integer.parseInt(getDate(getDate(0), "yyyy")) - Integer.parseInt(getDate(rs.getDate("CAR_AGE"), "yyyy"));
					map.put("carAge", carAge <= 0 ? 1 : carAge);
				}
				map.put("carDisplacement", rs.getBigDecimal("CAR_DISPLACEMENT"));
				map.put("carMileage", rs.getBigDecimal("CAR_MILEAGE"));
				map.put("carTransferNum", rs.getInt("CAR_TRANSFER_NUM"));
				return map;
			}
		};
		List<Map<String, Object>> list = this.queryForList(sql.toString(), mapper, params);
		if(list != null && !list.isEmpty()){
			return list.get(0);
		} else {
			return null;
		}
	}

	private StringBuilder getCarListSql(String sql, String whereSql, List<Car> cars){
		StringBuilder sb = new StringBuilder(sql);
		StringBuilder whereSb = new StringBuilder(whereSql);
		if(cars != null && cars.size() > 0) {
			sb.append(", (");
			for (int i = 0; i < cars.size(); i++) {
				if (i != 0) {
					sb.append("UNION ");
				}
				sb.append("SELECT ").append(cars.get(i).getCarId()).append(" AS CAR_ID ");
			}
			sb.append(") AS whereT ");
			whereSb.append("AND ec.CAR_ID = whereT.CAR_ID ");
		}
		return new StringBuilder(sb).append(whereSb);
	}

	private final static String selectCarOutStockCarAgeRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as storageNum, " +
			"bca.CAR_AGE_REMARK " +
			"FROM " +
			"ERP_car ec, " +
			"ERP_CAR_SALE ecs, " +
			"base_car_age bca " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.CAR_LICENCE_DATE >= bca.car_age_from " +
			"AND ec.CAR_LICENCE_DATE <= bca.car_age_to ";
	/**
	 * 查询出库车龄排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOutStockCarAgeRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOutStockCarAgeRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY bca.CAR_AGE_ID ");
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("carAgeRemark", rs.getString("CAR_AGE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOrderCarAgeRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as storageNum, " +
			"bca.CAR_AGE_REMARK " +
			"FROM " +
			"ERP_car ec, " +
			"ERP_CAR_ORDER eco, " +
			"base_car_age bca " +
			"WHERE " +
			"ec.CAR_ID = eco.CAR_ID " +
			"AND ec.CAR_LICENCE_DATE >= bca.car_age_from " +
			"AND ec.CAR_LICENCE_DATE <= bca.car_age_to ";
	/**
	 * 查询出库车龄排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOrderCarAgeRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOrderCarAgeRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY ec.CAR_AGE_ID ");
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("carAgeRemark", rs.getString("CAR_AGE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectStorageStockAgeRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as storageNum, " +
			"bsa.STOCK_AGE_REMARK " +
			"FROM " +
			"ERP_CAR ec, " +
			"BASE_STOCK_AGE bsa " +
			"WHERE " +
			"ec.CAR_CREATE_TIME >= bsa.STOCK_AGE_FROM " +
			"AND ec.CAR_CREATE_TIME <= bsa.STOCK_AGE_TO ";
	/**
	 * 查询库存车龄排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectStorageStockAgeRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectStorageStockAgeRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		sql.append("GROUP BY bsa.STOCK_AGE_ID " );
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("stockAgeRemark", rs.getString("STOCK_AGE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOutStockStockAgeRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as storageNum, " +
			"bsa.STOCK_AGE_REMARK " +
			"FROM " +
			"ERP_CAR ec, " +
			"ERP_CAR_SALE ecs, " +
			"BASE_STOCK_AGE bsa " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.CAR_CREATE_TIME >= bsa.STOCK_AGE_FROM " +
			"AND ec.CAR_CREATE_TIME <= bsa.STOCK_AGE_TO ";
	/**
	 * 查询出库车龄排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOutStockStockAgeRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOutStockStockAgeRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY bsa.STOCK_AGE_ID " );
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("stockAgeRemark", rs.getString("STOCK_AGE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarOrderStockAgeRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as storageNum, " +
			"bsa.STOCK_AGE_REMARK " +
			"FROM " +
			"ERP_CAR ec, " +
			"ERP_CAR_ORDER eco, " +
			"BASE_STOCK_AGE bsa " +
			"WHERE " +
			"ec.CAR_ID = eco.CAR_ID " +
			"AND ec.CAR_CREATE_TIME >= bsa.STOCK_AGE_FROM " +
			"AND ec.CAR_CREATE_TIME <= bsa.STOCK_AGE_TO ";
	/**
	 * 查询出库车龄排行
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Map<String, Object>> selectCarOrderStockAgeRank(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOrderStockAgeRankSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		sql.append("GROUP BY bsa.STOCK_AGE_ID " );
		sql.append("ORDER BY storageNum DESC ");
		RowMapper<Map<String, Object>> mapper = new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("storageNum", rs.getInt("storageNum"));
				map.put("stockAgeRemark", rs.getString("STOCK_AGE_REMARK"));
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params);
	}

	private final static String selectCarStorageListSql = "SELECT " +
			"ec.*, " +
			"bb.BRAND_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"WHERE ec.CAR_ID > 0 ";
	/**
	 * 查询车辆库存列表
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 * @throws Exception
	 */
	public List<Car> selectCarStorageList(Car car, Date startDate, Date endDate) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarStorageListSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private final static String selectCarStorageNumSql = "SELECT " +
			"COUNT(ec.CAR_ID) " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"WHERE 1=1 ";
	/**
	 * 查询车辆库存列表数量
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public int selectCarStorageNum(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarStorageNumSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, startDate, endDate);
		return this.queryForInt(sql.toString(), params);
	}

	private final static String selectCarOutStockListSql = "SELECT " +
			"ec.*, " +
			"bb.BRAND_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN ERP_CAR_SALE ecs ON ec.CAR_ID = ecs.CAR_ID " +
			"WHERE 1=1 ";
	/**
	 * 查询车辆出库列表
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 * @throws Exception
	 */
	public List<Car> selectCarOutStockList(Car car, Date startDate, Date endDate) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarOutStockListSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private final static String selectCarOutStockNumSql = "SELECT " +
			"COUNT(ec.CAR_ID) " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN ERP_CAR_SALE ecs ON ec.CAR_ID = ecs.CAR_ID " +
			"WHERE ec.CAR_ID > 0 ";
	/**
	 * 查询车辆出库列表数量
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public int selectCarOutStockNum(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOutStockNumSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND ecs.SALE_DEAL_DATE < ? ");
			params.add(endDate);
		}
		return this.queryForInt(sql.toString(), params);
	}

	private final static String selectCarOrderListSql = "SELECT " +
			"ec.*, " +
			"bb.BRAND_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN ERP_CAR_ORDER eco ON ec.CAR_ID = eco.CAR_ID " +
			"WHERE ec.CAR_ID > 0 ";
	/**
	 * 查询车辆预订列表
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 * @throws Exception
	 */
	public List<Car> selectCarOrderList(Car car, Date startDate, Date endDate) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarOrderListSql);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private final static String selectCarOrderNum = "SELECT " +
			"COUNT(ec.CAR_ID) " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN ERP_CAR_ORDER eco ON ec.CAR_ID = eco.CAR_ID " +
			"WHERE ec.CAR_ID > 0 ";
	/**
	 * 查询车辆预订列表数量
	 * @param car
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public int selectCarOrderNum(Car car, Date startDate, Date endDate) {
		StringBuilder sql = new StringBuilder(selectCarOrderNum);
		List<Object> params = new ArrayList<Object>();
		statisSearchCondition(sql, params, car, null, null);
		if(startDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME >= ? ");
			params.add(startDate);
		}
		if(endDate != null) {
			sql.append("AND eco.ORDER_CREATE_TIME < ? ");
			params.add(endDate);
		}
		return this.queryForInt(sql.toString(), params);
	}

	private final static String selectSimilarPriceCarSql = "SELECT " +
			"ec.*, " +
			"bb.BRAND_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM, " +
			"es.SHOP_MANAGE_NAME, " +
			"es.SHOP_MANAGE_MOBILE " +
			"FROM " +
			"ERP_CAR ec  " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"JOIN BASE_PRICE bp ON ec.CAR_RETAIL_PRICE >= bp.PRICE_FROM_10000 AND ec.CAR_RETAIL_PRICE <= bp.PRICE_TO_10000 AND bp.PRICE_ID = (SELECT T2.price_id FROM erp_car T1,base_price T2 WHERE T1.CAR_RETAIL_PRICE >= T2.PRICE_FROM_10000 AND T1.CAR_RETAIL_PRICE <= T2.PRICE_TO_10000 AND T1.car_id = ?) " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"WHERE 1=1 ";

	/**
	 * 查询相似价格车辆
	 * @param car 车辆信息
	 * @return
	 */
	public List<Car> selectSimilarPriceCar(Car car) throws Exception {
		StringBuilder sql = new StringBuilder(selectSimilarPriceCarSql);
		List<Object> params = new ArrayList<Object>();
		params.add(car.getMarketId());
		params.add(car.getCarId());
		car.setCarId(null);
		searchCondition(sql, params, car);
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	/**
	 * 分页Sql
	 * @param sql
	 * @param pageSize
	 */
	private void getLimitSql(StringBuilder sql, Integer pageSize) {
		if(pageSize != null && pageSize > 0) {
			sql.append("LIMIT ").append(pageSize);
		} else {
			sql.append("LIMIT 5");
		}
	}

	private final static String selectValiCarVinSql = "SELECT ec.* FROM erp_car ec WHERE ec.CAR_VIN = ? AND ec.CAR_STOCK_STATE < 40 ";
	/**
	 * 查询验证车架号
	 * @param car
	 * @return
	 */
	public List<Car> selectValiCarVin(Car car) throws Exception {
		StringBuilder sql = new StringBuilder(selectValiCarVinSql);
		List<Object> params = new ArrayList<Object>();
		params.add(car.getCarVin());
		return this.queryForList(sql.toString(), Car.class, params);
	}

	/**
	 * 通过车辆订阅列表查询对应的车辆信息列表
	 * 
	 * @param carSelectTemplateList多条车辆订阅信息，values封装查询条件
	 * @return 满足条件的carlist
	 * @throws Exception
	 */
	private final static String selectCarListByCarSelectTemplateListSql = "SELECT " +
			"ec.CAR_ID, "+
			"ec.CAR_TITLE, "+
			"ec.CAR_VIN, "+
			"ec.CAR_OUT_COLOR, "+
			"ec.CAR_IMAGE_URL, "+
			"ec.CAR_MILEAGE, "+
			"ec.CAR_RETAIL_PRICE, "+
			"ec.CAR_LICENCE_DATE, "+
			"ec.CAR_FACOTRY_DATE, "+
			"ec.CAR_FORCE_INSURANCE_DATE, "+
			"ec.CAR_LICENCE_STATE, "+
			"ec.CAR_ORDER_STATE, "+
			"ec.CAR_FIXED_PRICE_STATE, "+
			"ec.CAR_BARGAIN_PRICE_STATE, "+
			"ec.CAR_TRANSFER_STATE, "+
			"ec.CAR_SALE_STATE, "+
			"ec.CAR_SALE_TIME, "+
			"ec.CAR_DETECT_STATE, "+
			"ec.CAR_DISPLACEMENT, "+
			"ec.CAR_INLET_MODEL, "+
			"ec.CAR_GEARBOX, "+
			"ec.CAR_STOCK_STATE, "+
			"ec.CAR_STOCK_TIME, "+
			"ec.CAR_AUTH_STATE, "+
			"ec.CAR_RFID, "+
			"ec.CAR_RFID_TIME, "+
			"ec.CAR_PLEDGE_STATE, "+
			"ec.CAR_SITE_STATE, "+
			"ec.CAR_SITE_TIME, "+
			"ec.CAR_EXTENDED_WARRANTY, "+
			"ec.CAR_FINANCE_SERVICE, "+
			"ec.CAR_CREATE_TIME, "+
			"ec.CAR_UPDATE_TIME, "+
			"ec.BRAND_ID, "+
			"ec.SERIES_ID, "+
			"ec.MODEL_ID, "+
			"ec.CAR_TYPE_ID, "+
			"ec.USER_ID, "+
			"ec.MARKET_ID, "+
			"ec.REGION_ID, "+
			"ec.SHOP_ID, "+
			"ec.MARKET_CAR_ID, "+
			"ec.SALE_USER_ID, "+
			"bb.BRAND_NAME, " +
			"bs.SERIES_NAME, " + 
			"em.MARKET_NAME, " + 
			"er.REGION_NAME, " + 
			"es.SHOP_NAME, " + 
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " + 
			"es.SHOP_LEGAL_NAME, " + 
			"es.SHOP_LEGAL_MOBILE, " + 
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM, " + 
			"es.SHOP_MANAGE_NAME, " + 
			"es.SHOP_MANAGE_MOBILE, " + 
			"bc.CITY_NAME, " +
			"eu.USER_NAME, " + 
			"eu.USER_MOBILE " + 
			"FROM " + 
			"ERP_CAR ec " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " + 
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN BASE_CITY bc ON bc.CITY_ID = em.CITY_ID " +
			"LEFT JOIN BASE_CAR_AGE bca ON ec.CAR_LICENCE_DATE >= bca.CAR_AGE_FROM AND ec.CAR_LICENCE_DATE < bca.CAR_AGE_TO " +
			"LEFT JOIN BASE_CAR_MILEAGE bcm ON ec.CAR_MILEAGE >= bcm.CAR_MILEAGE_FROM AND ec.CAR_MILEAGE < bcm.CAR_MILEAGE_TO " +
			"LEFT JOIN ERP_USER eu ON ec.SALE_USER_ID = eu.USER_ID " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " + 
			"WHERE 1=1 ";

	public List<Car> selectCarListByCarSelectTemplateList(Car car, List<CarSelectTemplate> carSelectTemplateList) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarListByCarSelectTemplateListSql);
		List<Object> params = new ArrayList<Object>();
		//controller层已经判断过carSelectTemplateList为null或者长度为0的情况
		searchCarListByCarSelectTemplateListCondition(sql,params,carSelectTemplateList);
		searchCondition(sql, params, car);
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private void searchCarListByCarSelectTemplateListCondition(StringBuilder sql, List<Object> params, List<CarSelectTemplate> carSelectTemplateList){
		for (CarSelectTemplate carSelectTemplate : carSelectTemplateList) {
			if(carSelectTemplate.getTemplateValues() != null){
				String[] values = carSelectTemplate.getTemplateValues().split(",");
				sql.append("AND ( ");
				switch (carSelectTemplate.getTemplateKey()) {
				case "series_id":
					for (int i = 0; i < values.length; i++) {
						if (i == values.length - 1) {
							sql.append("ec.SERIES_ID = ? ) ");
							params.add(values[i]);
						} else {
							sql.append("ec.SERIES_ID = ? OR ");
							params.add(values[i]);
						}
					}
					break;
				case "city_id":
					for (int i = 0; i < values.length; i++) {
						if (i == values.length - 1) {
							sql.append("bc.CITY_ID = ? ) ");
							params.add(values[i]);
						} else {
							sql.append("bc.CITY_ID = ? OR ");
							params.add(values[i]);
						}
					}
					break;
				case "car_age_id":
					for (int i = 0; i < values.length; i++) {
						if (i == values.length - 1) {
							sql.append("bca.CAR_AGE_ID = ? ) ");
							params.add(values[i]);
						} else {
							sql.append("bca.CAR_AGE_ID = ? OR ");
							params.add(values[i]);
						}
					}
					break;
				case "car_mileage_id":
					for (int i = 0; i < values.length; i++) {
						if (i == values.length - 1) {
							sql.append("bcm.CAR_MILEAGE_ID = ? ) ");
							params.add(values[i]);
						} else {
							sql.append("bcm.CAR_MILEAGE_ID = ? OR ");
							params.add(values[i]);
						}
					}
					break;
				default:
					break;
				}
			}
		}
	}

	/**
	 * 查询车辆的全部信息，用于一键导入索引库
	 */
	private final static String selectAllCarSql = "SELECT " +
			"ec.*, "+
			"bb.BRAND_NAME, " +
			"bs.SERIES_NAME, " +
			"bm.MODEL_NAME, " +
			"bm.MODEL_YEAR, " +
			"bct.CAR_TYPE_NAME, " +
			"em.MARKET_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM, " +
			"es.SHOP_MANAGE_NAME, " +
			"es.SHOP_MANAGE_MOBILE, " +
			"bc.CITY_NAME, " +
			"eu1.USER_NAME AS CREATE_USER_NAME, " +
			"eu1.USER_MOBILE AS CREATE_USER_MOBILE, " +
			"eu1.USER_REMARK AS CREATE_USER_REMARK, " +
			"eu.USER_NAME, "+
			"eu.USER_MOBILE "+
			"FROM " +
			"ERP_CAR ec " +
			"JOIN BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN BASE_MODEL bm ON ec.MODEL_ID = bm.MODEL_ID " +
			"JOIN BASE_CAR_TYPE bct ON ec.CAR_TYPE_ID = bct.CAR_TYPE_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_USER eu1 ON ec.USER_ID = eu1.USER_ID " +
			"LEFT JOIN ERP_USER eu ON ec.SALE_USER_ID = eu.USER_ID "+
			"LEFT JOIN BASE_CAR_AGE bca ON ec.CAR_LICENCE_DATE >= bca.CAR_AGE_FROM AND ec.CAR_LICENCE_DATE < bca.CAR_AGE_TO " +
			"LEFT JOIN BASE_STOCK_AGE bsa ON ec.CAR_CREATE_TIME >= bsa.STOCK_AGE_FROM AND ec.CAR_CREATE_TIME < bsa.STOCK_AGE_TO " +
			"LEFT JOIN ERP_REGION er ON ec.REGION_ID = er.REGION_ID " +
			"LEFT JOIN ERP_CAR_SALE ecs ON ec.CAR_ID = ecs.CAR_ID "+
			"LEFT JOIN ERP_CAR_ORDER eco ON ec.CAR_ID = eco.CAR_ID " +
			"LEFT JOIN base_city bc ON bc.city_id = em.city_id " +
			"WHERE 1=1 ";

	public List<ESCar> selectAllCar(Car car) throws Exception{
		StringBuilder sql = new StringBuilder(selectAllCarSql);
		List<Object> params = new ArrayList<>();
		sql = new StringBuilder("SELECT * FROM (").append(sql).append(") car ");
		return this.queryForList(sql.toString(), ESCar.class, params);
	}
}
